package com.util;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.generate.bean.ColumnBean;
import com.generate.bean.DBTableBean;


public final class DataBaseInfo{
	DBProcBean dbBean = new DBProcBean();
	/**  
	* 获取数据库Table列表  ,只取Table,不取其它类型（如views等）
	* @param schemaPattern按Schema查询，可为Null
	* @return DBTableBean的列表，内容只有schema和tablename
	* @throws Exception  
	*/  
	public List<DBTableBean> getTableList(String schemaPattern) throws Exception{
		Connection conn=null;
		ResultSet rs=null;
			try  {
				conn=dbBean.getConn();
				DatabaseMetaData dbmd=conn.getMetaData();
				/**  
				* 获取类别  
				*/  
				//rs=dbmd.getCatalogs();
				/**  
				* 获取模式  
				*/  
				//rs=dbmd.getSchemas();
				/**  
				* 限定获取表的类型  
				*/  
				String types[]={"TABLE"};
				/**  
				* 获取表(TABLE)信息  
				* TABLE_CAT String => 表类别（可为 null）
				* TABLE_SCHEM String => 表模式（可为 null）
				* TABLE_NAME String => 表名称
				* TABLE_TYPE String => 表类型。典型的类型是 "TABLE"、"VIEW"、"SYSTEM TABLE"、"GLOBAL TEMPORARY"、"LOCAL TEMPORARY"、"ALIAS" 和 "SYNONYM"。
				* REMARKS String => 表的解释性注释
				* TYPE_CAT String => 类型的类别（可为 null）
				* TYPE_SCHEM String => 类型模式（可为 null）
				* TYPE_NAME String => 类型名称（可为 null）
				* SELF_REFERENCING_COL_NAME String => 有类型表的指定 "identifier" 列的名称（可为 null）
				* REF_GENERATION String => 指定在 SELF_REFERENCING_COL_NAME 中创建值的方式。这些值为 "SYSTEM"、"USER" 和 "DERIVED"。（可能为 null）
				*/  
				rs=dbmd.getTables(null, schemaPattern, null, types);

				/**  
				* 以下部分为结果集解析  
				*/  
				ResultSetMetaData rsmd=rs.getMetaData();
				int size=rsmd.getColumnCount();

				List<DBTableBean> rows=new ArrayList<DBTableBean>();
				//Map item=null;
				while(rs.next()){
					DBTableBean tblBean = new DBTableBean();
					tblBean.setTablename(rs.getString("TABLE_NAME"));
					tblBean.setSchema(rs.getString("TABLE_SCHEM"));
					rows.add(tblBean);
				}
				rs.close();
				dbBean.close();
				return rows;
				
			}catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
				dbBean.close();
				return null;
			}
	}
	/**
	 * 取指定数据库的Schema
	 * @return Schema的列表
	 */
	public List<String> getAllSchemas(){  
		Connection conn=null;
		List<String> rtn = null;
		conn=dbBean.getConn();
		try{  
			DatabaseMetaData dbmd=conn.getMetaData();
			rtn = new ArrayList<String>();
			ResultSet rs = dbmd.getSchemas();  
			while (rs.next()){  
				String tableSchem = rs.getString("TABLE_SCHEM");
				rtn.add(tableSchem);
			}  
			dbBean.close();
		} catch (SQLException e)           {  
			// TODO: handle SQLException               e.printStackTrace();  
			dbBean.close();
		}
		return rtn;
    }  
	
	public DBTableBean getTableColumns(String schemaName, String tableName){  
		Connection conn=null;
		DBTableBean tblRtn = null;
        try{  
    		conn=dbBean.getConn();
    		DatabaseMetaData dbmd=conn.getMetaData();
            ResultSet rs = dbmd.getColumns(null, schemaName, tableName,null);
            rs.last();
            int size = rs.getRow();
            rs.beforeFirst();
            if (size <1) return null;
//            System.out.println("--all columns is "+size);
            List<ColumnBean> colBeans = new ArrayList<ColumnBean>();
            String pmKey = "";
            StringBuffer crsCols = new StringBuffer();
            while (rs.next()){  

//				ResultSetMetaData rsmd=rs.getMetaData();
//				 for (int i = 1; i <= rsmd.getColumnCount(); i++) {
//					 System.out.println(rsmd.getColumnName(i)+"  :::  "+rs.getString(i));
//				 }
//				 System.out.println("------next--------");
                // table catalog (may be null)
            	String tableCat = rs.getString("TABLE_CAT");
            	// table schema (may be null)
            	String tableSchemaName = rs.getString("TABLE_SCHEM");
            	// table name
//            	String tableName_ = rs.getString("TABLE_NAME");
            	// column name
            	String columnName = rs.getString("COLUMN_NAME");
            	// SQL type from java.sql.Types
            	int dataType = rs.getInt("DATA_TYPE");
            	// Data source dependent type name, for a UDT the type name is
            	// fully qualified
            	String dataTypeName = rs.getString("TYPE_NAME");
            	// table schema (may be null)
            	int columnSize = rs.getInt("COLUMN_SIZE");
            	// the number of fractional digits. Null is returned for data
            	// types where DECIMAL_DIGITS is not applicable.
            	int decimalDigits = rs.getInt("DECIMAL_DIGITS");
            	// Radix (typically either 10 or 2)
            	int numPrecRadix = rs.getInt("NUM_PREC_RADIX");
            	// is NULL allowed.
            	int nullAble = rs.getInt("NULLABLE"); 
            	// comment describing column (may be null) 
            	String remarks = rs.getString("REMARKS"); 
            	// default value for the column, which should be interpreted as 
            	// a string when the value is enclosed in single quotes (may be
            	// null)
            	String columnDef = rs.getString("COLUMN_DEF"); 
            	// for char types the maximum number of bytes in the column
            	int charOctetLength = rs.getInt("CHAR_OCTET_LENGTH");
            	// index of column in table (starting at 1)
            	int ordinalPosition = rs.getInt("ORDINAL_POSITION");
            	// ISO rules are used to determine the nullability for a column.
            	// YES --- if the parameter can include NULLs;
            	// NO --- if the parameter cannot include NULLs
            	// empty string --- if the nullability for the parameter is
            	// unknown
            	String isNullAble = rs.getString("IS_NULLABLE");
            	// Indicates whether this column is auto incremented 
            	// YES --- if the column is auto incremented
            	// NO --- if the column is not auto incremented
            	// empty string --- if it cannot be determined whether the
            	String isAutoNcrement = "";
            	if (dbBean.getStrDbtype().equals("MSSQL2000")){
            		//SQL server 2000，判断是否自增长方法，由dataTypeName是否含有identity 为主
            		if (dataTypeName.indexOf("identity")>-1){
            			isAutoNcrement = "YES";
            		}else{
            			isAutoNcrement = "NO";
            		}
            	}else{
            		isAutoNcrement = rs.getString("IS_AUTOINCREMENT");
            	}
//            	System.out.println(tableCat + "-" + tableSchemaName + "-" + tableName_ + "-" + columnName + "-"
//            			+ dataType + "-" + dataTypeName + "-" + columnSize + "-" + decimalDigits + "-" + numPrecRadix
//            			+ "-" + nullAble + "-" + remarks + "-" + columnDef + "-" +  charOctetLength + "-" + ordinalPosition + "-" + isNullAble + "-"+isAutoNcrement);
            	if (tblRtn == null){
            		tblRtn = new DBTableBean();
            		tblRtn.setColSize(size);
            		tblRtn.setSchema(tableSchemaName);
            		tblRtn.setTablename(tableName);
            	}//if
            	ColumnBean colbean = new ColumnBean();
            	colbean.setColumnName(columnName);
            	colbean.setColumnDef(columnDef);
            	colbean.setDataType(dataType);
            	colbean.setDataTypeName(dataTypeName);
            	colbean.setColumnSize(columnSize);
            	colbean.setIsNullAble(isNullAble);
            	colbean.setIsAutoNcrement(isAutoNcrement);
            	//目前判断主键的标准： 是否自动增加
            	if ("YES".equalsIgnoreCase(colbean.getIsAutoNcrement())){
            		colbean.setId(true);
            		pmKey = colbean.getColumnName();
            	}
            	colBeans.add(colbean);
            	if(!colbean.isId()) crsCols.append(colbean.getColumnName()+",");
            }  //while
            if (tblRtn != null){
            	tblRtn.setColumnBeans(colBeans);
            	tblRtn.setPmKey(pmKey);
                crsCols.deleteCharAt(crsCols.length()-1);
            	tblRtn.setAllColumns(crsCols.toString());
            }
            
        } catch (SQLException e){  
        	e.printStackTrace();
            // TODO: handle SQLException               e.printStackTrace();  
        }   catch (Exception e){  
        	e.printStackTrace();
        }
        return tblRtn;
    }  
	 /** 
     * 获得一个表的主键信息 
     */  
    public void getAllPrimaryKeys(String schemaName, String tableName) {  
		Connection conn=null;
		DBTableBean tblRtn = null;
        try{  
    		conn=dbBean.getConn();
    		DatabaseMetaData dbmd=conn.getMetaData();
            ResultSet rs = dbmd.getPrimaryKeys(null, schemaName, tableName);  
            while (rs.next()){  
                String columnName = rs.getString("COLUMN_NAME");//列名  
                short keySeq = rs.getShort("KEY_SEQ");//序列号(主键内值1表示第一列的主键，值2代表主键内的第二列)  
                String pkName = rs.getString("PK_NAME"); //主键名称    
                System.out.println(columnName + "-" + keySeq + "-" + pkName);     
            }  
        }catch (SQLException e){  
            e.printStackTrace();  
        }  
    }  
  
	 /** 
     * 获得一个表的外键信息 
     */  
    public void getAllExportedKeys(String schemaName, String tableName) {  

		Connection conn=null;
		DBTableBean tblRtn = null;
        try{  
    		conn=dbBean.getConn();
    		DatabaseMetaData dbmd=conn.getMetaData();
            ResultSet rs = dbmd.getExportedKeys(null, schemaName, tableName);  
            while (rs.next()){  
                String pkTableCat = rs.getString("PKTABLE_CAT");//主键表的目录（可能为空）  
                String pkTableSchem = rs.getString("PKTABLE_SCHEM");//主键表的架构（可能为空）  
                String pkTableName = rs.getString("PKTABLE_NAME");//主键表名   
                String pkColumnName = rs.getString("PKCOLUMN_NAME");//主键列名    
                String fkTableCat = rs.getString("FKTABLE_CAT");//外键的表的目录（可能为空）出口（可能为null）  
                String fkTableSchem = rs.getString("FKTABLE_SCHEM");//外键表的架构（可能为空）出口（可能为空）  
                String fkTableName = rs.getString("FKTABLE_NAME");//外键表名  
                String fkColumnName = rs.getString("FKCOLUMN_NAME"); //外键列名                  
                short keySeq = rs.getShort("KEY_SEQ");//序列号（外键内值1表示第一列的外键，值2代表在第二列的外键）。  
                  
                /** 
                 * hat happens to foreign key when primary is updated:  
                 * importedNoAction - do not allow update of primary key if it has been imported 
                 * importedKeyCascade - change imported key to agree with primary key update  
                 * importedKeySetNull - change imported key to NULL if its primary key has been updated 
                 * importedKeySetDefault - change imported key to default values if its primary key has been updated 
                 * importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x compatibility)    
                 */  
                short updateRule = rs.getShort("UPDATE_RULE");  
                  
                /** 
                 * What happens to the foreign key when primary is deleted. 
                 * importedKeyNoAction - do not allow delete of primary key if it has been imported 
                 * importedKeyCascade - delete rows that import a deleted key  
                 * importedKeySetNull - change imported key to NULL if its primary key has been deleted  
                 * importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x compatibility) 
                 * importedKeySetDefault - change imported key to default if its primary key has been deleted    
                 */  
                short delRule = rs.getShort("DELETE_RULE");  
                String fkName = rs.getString("FK_NAME");//外键的名称（可能为空）  
                String pkName = rs.getString("PK_NAME");//主键的名称（可能为空）  
                  
                /** 
                 * can the evaluation of foreign key constraints be deferred until commit 
                 * importedKeyInitiallyDeferred - see SQL92 for definition 
                 * importedKeyInitiallyImmediate - see SQL92 for definition  
                 * importedKeyNotDeferrable - see SQL92 for definition    
                 */  
                short deferRability = rs.getShort("DEFERRABILITY");  
                  
                System.out.println(pkTableCat + "-" + pkTableSchem + "-" + pkTableName + "-" + pkColumnName + "-"    
                        + fkTableCat + "-" + fkTableSchem + "-" + fkTableName + "-" + fkColumnName + "-" + keySeq + "-"    
                        + updateRule + "-" + delRule + "-" + fkName + "-" + pkName + "-" + deferRability);     
            }  
        } catch (SQLException e){  
            e.printStackTrace();     
        }  
    }  
} 

